﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Core.ExpressionHelper;

namespace Core.NpoiHelper
{
    public class NpoiHelper
    {
        private static Dictionary<string, string> FileType = new Dictionary<string, string>()
        {
            {"255216","jpg" },
            {"208207","doc xls ppt wps" },
            {"8075","docx pptx xlsx zip" },
            {"5150","txt" },
            {"8297","rar" },
            {"7790","exe" },
            {"3780","pdf" }
        };
        /// <summary>
        /// 初始化一个workbook
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <returns></returns>
        public IWorkbook InitWorkBook(string FilePath)
        {
            if (!File.Exists(FilePath)) throw new Exception("文件并不存在！");
            Stream fs = File.Open(FilePath, FileMode.Open);
            IWorkbook workbook;
            string ExtendName = Path.GetExtension(FilePath);
            if (ExtendName.ToLower() == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (ExtendName.ToLower() == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                throw new Exception("文件后缀名不符合要求!");
            }
            return workbook;
        }
        /// <summary>
        /// 把excel转成datatable
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string FilePath)
        {
            var workbook = InitWorkBook(FilePath);
            var sheet = workbook.GetSheetAt(0);
            IRow row;
            ICell cell;
            //先把header取出
            row = sheet.GetRow(0);
            int RowNum = row.RowNum;
            int ColumnNum = sheet.PhysicalNumberOfRows;
            DataTable dataTable = new DataTable();
            //构造dt表头
            for (int i = 0; i < RowNum - 1; i++)
            {
                cell = row.GetCell(i);
                DataColumn column = new DataColumn(cell.StringCellValue);
                dataTable.Columns.Add(column);
            }
            for (int i = 1; i < ColumnNum - 1; i++)
            {
                row = sheet.GetRow(i);
                DataRow data = dataTable.NewRow();
                for (int j = 0; j < RowNum - 1; j++)
                {
                    cell = row.GetCell(j);
                    data[j] = cell;
                }
                dataTable.Rows.Add(data);
            }
            return dataTable;
        }

        public List<T> ExcelToObject<T>(string FilePath) where T : class, new()
        {
            var workbook = InitWorkBook(FilePath);
            var sheet = workbook.GetSheetAt(0);
            return SheetToObject<T>(sheet);
        }
        public List<T> ExcelToObject<T>(string FilePath, string SheetName) where T : class, new()
        {
            var workbook = InitWorkBook(FilePath);
            var sheet = workbook.GetSheet(SheetName);
            return SheetToObject<T>(sheet);
        }
        public List<T> ExcelToObject<T>(string FilePath, int SheetIndex) where T : class, new()
        {
            var workbook = InitWorkBook(FilePath);
            var sheet = workbook.GetSheetAt(SheetIndex);
            return SheetToObject<T>(sheet);
        }

        private List<T> SheetToObject<T>(ISheet sheet) where T : class, new()
        {
            List<T> list = new List<T>();
            IRow row;
            ICell cell;
            //表头列
            var HeaderRow = sheet.GetRow(0);
            int RowNum = HeaderRow.LastCellNum;
            int ColumnNum = sheet.PhysicalNumberOfRows;
            //映射关系
            var dd = GetPropNames<T>();
            //数据列
            for (int i = 1; i <= ColumnNum - 1; i++)
            {
                row = sheet.GetRow(i);
                T entity = new T();
                for (int j = 0; j <= RowNum - 1; j++)
                {
                    var header = HeaderRow.GetCell(j);
                    if (header == null)
                    {
                        continue;
                    }
                    var HeaderCellValue = header.ToString();
                    cell = row.GetCell(j);
                    if (!dd.TryGetValue(HeaderCellValue.ToString(), out string propName))
                    {
                        continue;
                    }
                    var EntityProp = typeof(T).GetProperty(propName);
                    var value = Convert.ChangeType(GetCellValue(cell), EntityProp.PropertyType);
                    ExpressionHelper.ExpressionHelper.GetSetter<T>(EntityProp)(entity, value);
                }
                list.Add(entity);
            }
            return list;
        }

        /// <summary>
        /// 初始化一个workbook
        /// </summary>
        /// <param name="fs"></param>
        /// <returns></returns>
        private IWorkbook InitWorkBook(FileStream fs)
        {
            IWorkbook workbook;
            string ExtendName = GetExtension(fs);
            if (ExtendName.ToLower().Contains("xls"))
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (ExtendName.ToLower().Contains("xlsx"))
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                throw new Exception("文件后缀名不符合要求!");
            }
            return workbook;
        }

        public string GetExtension(FileStream fs)
        {
            BinaryReader reader = new BinaryReader(fs);
            string fileclass = "";
            // byte buffer;
            try
            {
                for (int i = 0; i < 2; i++)
                {
                    fileclass += reader.ReadByte().ToString();
                }

            }
            catch (Exception)
            {

                throw;
            }
            return FileType[fileclass];
        }
        /// <summary>
        /// 获取映射列名
        /// </summary>
        /// <param name="prop"></param>
        /// <returns></returns>
        private static string GetMapperName(PropertyInfo prop)
        {
            var Attribute = prop.GetCustomAttribute<NpoiAttribute>();
            if (Attribute == null)
            {
                return "";
            }
            else
            {
                return Attribute.ColumnMapperName;
            }
        }
        /// <summary>
        /// 获取实体和excel表头的映射关系
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private Dictionary<string, string> GetPropNames<T>() where T : class, new()
        {
            Dictionary<string, string> dd = new Dictionary<string, string>();
            Type t = typeof(T);
            foreach (var prop in t.GetProperties())
            {
                var attr = prop.GetCustomAttribute<NpoiAttribute>();
                if (attr != null)
                {
                    dd.Add(attr.ColumnMapperName, prop.Name);
                }
            }
            return dd;
        }
        /// <summary>
        /// 获取cell的值,并转换为相应的类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private object GetCellValue(ICell cell)
        {
            if (cell == null) return "";
            object value = null;
            switch (cell.CellType)
            {
                case CellType.Numeric:    //用于取出数值和公式类型的数据 
                    value = DateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString("yyyy-MM-dd") : cell.NumericCellValue.ToString();
                    break;
                case CellType.Error:
                    value = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.String:
                            string strFORMULA = cell.StringCellValue;
                            if (strFORMULA != null && strFORMULA.Length > 0)
                            {
                                value = strFORMULA.ToString();
                            }
                            break;
                        case CellType.Numeric:
                            value = Convert.ToString(cell.NumericCellValue);
                            break;
                        case CellType.Boolean:
                            value = Convert.ToString(cell.BooleanCellValue);
                            break;
                        case CellType.Error:
                            value = ErrorEval.GetText(cell.ErrorCellValue);
                            break;
                        default:
                            value = "";
                            break;
                    }
                    break;
                case CellType.Boolean:
                    // Boolean type
                    value = cell.BooleanCellValue.ToString();
                    break;

                case CellType.Blank:
                    break;

                default:
                    // String type
                    value = cell.StringCellValue.Trim();
                    break;
            }
            return value;
        }
    }
}
